const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const warehouseConfig = require('./warehouseConfig');

// 创建或连接到SQLite数据库
const dbPath = path.join(__dirname, '../../database.sqlite');
const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('数据库连接失败:', err.message);
    } else {
        console.log('已连接到SQLite数据库');
        initializeDatabase();
    }
});

// 初始化数据库表
function initializeDatabase() {
    // 创建用户表
    db.run(`CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT DEFAULT 'user',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`, (err) => {
        if (err) {
            console.error('创建用户表失败:', err.message);
        } else {
            console.log('用户表已创建或已存在');
            // 创建默认管理员用户
            createDefaultAdmin();
        }
    });

    // 创建货物表
    db.run(`CREATE TABLE IF NOT EXISTS goods (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        sku TEXT UNIQUE NOT NULL,
        quantity INTEGER DEFAULT 0,
        location TEXT,
        status TEXT DEFAULT 'inStock',
        batch_number TEXT,
        expiry_date DATETIME,
        specification TEXT,
        unit TEXT DEFAULT '件',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`, (err) => {
        if (err) {
            console.error('创建货物表失败:', err.message);
        } else {
            console.log('货物表已创建或已存在');
        }
    });

    // 创建入库单据表
    db.run(`CREATE TABLE IF NOT EXISTS inbound_documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        goods_id INTEGER NOT NULL,
        supplier TEXT NOT NULL,
        quantity INTEGER DEFAULT 0,
        expected_arrival_date DATETIME,
        actual_arrival_date DATETIME,
        status TEXT DEFAULT 'pending',
        operator TEXT,
        notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (goods_id) REFERENCES goods (id)
    )`, (err) => {
        if (err) {
            console.error('创建入库单据表失败:', err.message);
        } else {
            console.log('入库单据表已创建或已存在');
        }
    });

    // 创建出库单据表
    db.run(`CREATE TABLE IF NOT EXISTS outbound_documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        goods_id INTEGER NOT NULL,
        customer TEXT NOT NULL,
        quantity INTEGER DEFAULT 0,
        expected_pickup_date DATETIME,
        actual_pickup_date DATETIME,
        status TEXT DEFAULT 'pending',
        operator TEXT,
        notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (goods_id) REFERENCES goods (id)
    )`, (err) => {
        if (err) {
            console.error('创建出库单据表失败:', err.message);
        } else {
            console.log('出库单据表已创建或已存在');
        }
    });

    // 创建货位表
    db.run(`CREATE TABLE IF NOT EXISTS locations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        location_code TEXT UNIQUE NOT NULL,
        aisle TEXT NOT NULL,
        row TEXT NOT NULL,
        column TEXT NOT NULL,
        level TEXT NOT NULL,
        status TEXT DEFAULT 'enabled', -- enabled/disabled
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`, (err) => {
        if (err) {
            console.error('创建货位表失败:', err.message);
        } else {
            console.log('货位表已创建或已存在');
            // 初始化货位数据
            initializeLocationsFromConfig();
        }
    });
}

// 从配置文件初始化货位数据
function initializeLocationsFromConfig() {
    // 检查是否已有数据
    db.get("SELECT COUNT(*) as count FROM locations", (err, row) => {
        if (err) {
            console.error('检查货位表数据失败:', err.message);
            return;
        }
        
        // 如果没有数据，则从配置文件初始化货位
        if (row.count === 0) {
            const locations = [];
            const warehouseCode = warehouseConfig.warehouse.code;
            
            // 遍历所有巷道
            warehouseConfig.aisles.forEach(aisle => {
                const aisleCode = aisle.code;
                
                // 生成该巷道的所有货位
                for (let r = 1; r <= aisle.rows; r++) {
                    for (let c = 1; c <= aisle.columns; c++) {
                        for (let l = 1; l <= aisle.levels; l++) {
                            const locationCode = `${warehouseCode}-${aisleCode}-${r.toString().padStart(2, '0')}-${c.toString().padStart(2, '0')}-${l.toString().padStart(2, '0')}`;
                            locations.push({
                                location_code: locationCode,
                                aisle: aisleCode,
                                row: r.toString().padStart(2, '0'),
                                column: c.toString().padStart(2, '0'),
                                level: l.toString().padStart(2, '0'),
                                status: 'enabled'
                            });
                        }
                    }
                }
            });
            
            // 插入货位数据
            if (locations.length > 0) {
                const stmt = db.prepare("INSERT INTO locations (location_code, aisle, row, column, level, status) VALUES (?, ?, ?, ?, ?, ?)");
                locations.forEach(loc => {
                    stmt.run(loc.location_code, loc.aisle, loc.row, loc.column, loc.level, loc.status);
                });
                stmt.finalize();
                console.log(`从配置文件初始化了 ${locations.length} 个货位`);
            }
        } else {
            console.log('货位数据已存在，跳过初始化');
        }
    });
}

// 创建默认管理员用户
function createDefaultAdmin() {
    const defaultAdmin = {
        username: 'admin',
        password: '123456', // 简单密码用于测试
        role: 'admin'
    };

    const insertAdmin = `INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)`;
    db.run(insertAdmin, [defaultAdmin.username, defaultAdmin.password, defaultAdmin.role], (err) => {
        if (err) {
            console.error('创建默认管理员失败:', err.message);
        } else {
            console.log('默认管理员用户已创建或已存在');
        }
    });
}

module.exports = db;